package com.tskj.mh.studyroom.dao;

import com.alibaba.fastjson.JSONArray;
import com.tskj.mh.studyroom.dao.mysql.DbUtilitys;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Component
public class StudyRoomDAO {

    @Autowired
    private DbUtilitys dbUtilitys;

    public JSONArray getStudyRoomList() {
        String sql = "select * from studyroom order by sname";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    public JSONArray getTimeAndCountList(String roomId, String date) {
        String sql = "select study_room_time.tid,study_room_time.starttime, study_room_time.endtime " +
                ", studyroom.sname, (study_room_time.snum - ifnull(a.count, 0)) snum  from study_room_time " +
                "left join studyroom on studyroom.sid=? " +
                "left join (select starttime, sname, count(*) as count from study_order where startdate=? and orderstate='0' group by sname, starttime) a on cast(a.starttime as char(8)) = cast(study_room_time.starttime as char(8)) and a.sname=studyroom.sname " +
                "where study_room_time.roomid=? order by starttime";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, roomId, date, roomId);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    public int getOrderByAdUserName(String adUserName, String date) {
        String sql = "select ifnull(count(*), 0) count from study_order where adusername=? and createdate between ? and ? and orderstate='0'";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName, date + " 00:00:00", date + " 23:59:59");
        if (list == null || list.size() == 0) {
            return 0;
        }
        return Integer.valueOf(list.get(0).get("count").toString());
    }

    public JSONArray getOrderList(String adUserName, String date) {
        String sql = "select starttime from study_order where adusername=? and startdate=? and orderstate='0'";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName, date);
        if (list == null || list.size() == 0) {
            return new JSONArray();
        }
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    public String getStudyRoomId(String studyRoomName) {
        String sql = "select sid from studyroom where sname=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, studyRoomName);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sid");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public String getStudyRoomSortNo(String roomId, String roomName) {
        String sql = "select sortnum from studyroom where sid=? and sname=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, roomId, roomName);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public String getTimeSortNo(String roomId, String timeId) {
        String sql = "select sortnum from study_room_time where roomid=? and tid=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, roomId, timeId);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public String getMaxOrderNo(String roomName, String startDate, String startTime, String endTime) {
        String sql = "select (ifnull(max(sortnum), 0) + 1) sortnum from study_order where sname=? and starttime=? and endtime=? and startdate=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, roomName, startTime, endTime, startDate);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    /**
     * 检查此浴室的此日期的此时间段是否已订满
     * @param roomName
     * @param startTime
     * @param endTime
     * @param status
     * @param startDate
     * @return
     */
    public boolean canSave(String roomName, String startTime, String endTime, int status, String startDate) {
        String sql = "select (study_room_time.snum - ifnull(a.count, 0)) emptycount from study_room_time left join " +
                "(select count(*) count, sname, starttime, endtime from study_order where startdate=? and sname=? and starttime=? and endtime=? and orderstate=0 " +
                ") a on a.starttime = study_room_time.starttime and a.endtime=study_room_time.endtime " +
                "right join studyroom on studyroom.sid=study_room_time.roomid and studyroom.sname=? " +
                "where study_room_time.starttime=? and study_room_time.endtime=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, startDate, roomName, startTime, endTime, roomName, startTime, endTime);
        if (list == null || list.size() == 0) {
            return false;
        }
        Object value = list.get(0).get("emptycount");
        if (value == null || "".equals(value.toString()) || "0".equals(value.toString())) {
            return false;
        }
        return true;
    }

    public void save(String id, String studentName, String sex, int maxOrderNo, String roomName, String startTime, String endTime, int status, String code, String startDate, String adUserName) throws Exception {
        String sql = "insert into study_order (orderid, userid, sortnum, sname, starttime, endtime, orderstate, qrcode, startdate, adusername, createdate) "+
                "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now())";
        dbUtilitys.execSQLWithTran(sql, id, studentName, maxOrderNo, roomName, startTime, endTime, status, code, startDate, adUserName);
    }
}
